package com.sxfq.loansupermarket.backsystem.modular.tcCenter.controller;

import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.entity.MembersUser;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.service.MemberProductService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.FirstCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.ScoreDealInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerDetailsService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerPublicService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.basicData.service.IPayServiceService;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.CaseInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.DoctorOperationInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.ToothSite;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import java.math.BigDecimal;

import java.util.*;

/**
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/consultantTwo")
public class ConsultantTwoController extends BaseController {
    @Autowired
    IConsultantService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    IPayServiceService payServiceService;

    @Autowired
    private MemberProductService memberProductService;

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    IReservationsService reservationsService;

    @Autowired
    ICustomerDetailsService customerDetailsService;

    private String PREFIX = "/tcCenter/consultant/";

    /**
     * TC跳转指定tab页面 (获取开单操作  和 开单记录的 数据)   传一个客户ID和 预约ID
     */
    @RequestMapping(value = "/consultantToDetailByTabTest/{id}/{tabId}/{isKz}")
    public String consultantToDetailByTabTest(@PathVariable("id") String id, @PathVariable("tabId") String tabId, @PathVariable("isKz") String isKz, Model model) throws Exception {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String tableName1 = tableHead + "deal_info";
            String tableName = tableHead + "recharge_info";
            CustomerInfo customerInfo1 = customerService.selectOneById(id);   //当前记录的患者信息
            CustomerInfo customerInfoLast = customerService.selectOneByReservationsIdLast(customerInfo1.getReservationsId());  //最后一次次到店患者信息
            String sqlresrvation = "select id,huifang from reservations_info where id='" + customerInfo1.getReservationsId() + "' limit 0,1";
            String sqlresrvationsc = "select id from reservations_infosc where id='" + customerInfo1.getReservationsId() + "' limit 0,1";
            ReservationsInfo reservationscInfo = sqlMapper.selectOne(sqlresrvationsc, ReservationsInfo.class);
            if (reservationscInfo != null) {
                String reservationsInfosql = "select * from reservations_infosc where id='" + customerInfo1.getReservationsId() + "' limit 0,1";
                ReservationsInfo reservationsInfo = sqlMapper.selectOne(reservationsInfosql, ReservationsInfo.class);
                model.addAttribute("reservationsInfo", reservationsInfo);
            }
            ReservationsInfo reservationInfo = sqlMapper.selectOne(sqlresrvation, ReservationsInfo.class);
            String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + customerInfo1.getReservationsId() + "' ";
            List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
            String dealTables = "";
            String rechargeTables = "";
            String customerTables = "";
            String customerTablesd = "";
            for (int i = 0; i < abbreviationList.size(); i++) {
                String head = abbreviationList.get(i);
                if (i == abbreviationList.size() - 1) {
                    dealTables += "select * from " + head + "deal_info where reservations_id='" + customerInfo1.getReservationsId() + "' ";
                    rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + customerInfo1.getReservationsId() + "' ";
                    customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                            "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                            "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                            "t.appointment_pay_service as appointmentPayService " +
                            "from " + head + "customer_info t where reservations_id='" + customerInfo1.getReservationsId() + "' ";
                    customerTablesd += "select id,appointment_pay_service,arrival_time,archiving,field_tc from " + head + "customer_info t where reservations_id='" + customerInfo1.getReservationsId() + "' and arrival_time is not null";
                } else {
                    dealTables += "select * from " + head + "deal_info where reservations_id='" + customerInfo1.getReservationsId() + "' union ";
                    rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + customerInfo1.getReservationsId() + "' union ";
                    customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                            "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                            "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                            "t.appointment_pay_service as appointmentPayService " +
                            "from " + head + "customer_info t where reservations_id='" + customerInfo1.getReservationsId() + "' union ";
                    customerTablesd += "select id,appointment_pay_service,arrival_time,archiving,field_tc " +
                            "from " + head + "customer_info t where reservations_id='" + customerInfo1.getReservationsId() + "' " +
                            "and arrival_time is not null union ";
                }
            }
            if (reservationInfo == null) {
                reservationsService.insert(customerInfo1);
                model.addAttribute("huifang", "");
            } else {
                model.addAttribute("huifang", reservationInfo.getHuifang());
            }
            List<PayServicedInfo> payServiceInfoList = payServiceService.getPayServiceInfoList();
            //账单记录
            Double totalServiceActualPay = 0.00;
            Double totalArrears = 0.00;
            String custDealSql = "select t1.id2 as id2," +
                    "t1.reservations_id as reservationsId, " +
                    "t1.outpatient_name as outpatientName," +
                    "left(t1.operation_time,19) as operationTime," +
                    "t1.deal_status as dealStatus," +
                    "GROUP_CONCAT( distinct (t1.doctor)) as doctor,GROUP_CONCAT( distinct (t1.nurse)) as nurse," +
                    "t1.tc_name as tcName," +
                    "sum(t1.service_actual_pay+t1.arrears+t1.deduction_amount+t1.deposit_amount) AS foldingPay," +
                    "sum(t1.service_actual_pay) as serviceActualPay," +
                    "sum(t1.arrears) as arrears," +
                    "sum(t1.deduction_amount) as deductionAmount," +
                    "sum(t1.deposit_amount) as depositAmount," +
                    "t1.remarks as  remarks,(select distinct(r1.document_number) from (" + rechargeTables + ") r1 where r1.deal_id=t1.id group by r1.deal_id) as documentNumber " +
                    "from (" + dealTables + ") t1 where t1.deal_status='已结账'and t1.reservations_id = '" + customerInfo1.getReservationsId() + "' " +
                    "group by t1.operation_time order by t1.operation_time desc";
            List<PayServicedInfo> custDealList = sqlMapper.selectList(custDealSql, PayServicedInfo.class);
            for (int i = 0; i < custDealList.size(); i++) {
                List<PayServicedInfo> dealList = getDealInfoList(custDealList.get(i).getOperationTime(), custDealList.get(i).getReservationsId());
                custDealList.get(i).setDealList(dealList);
                custDealList.get(i).setDocumentNumber(dealList.get(0).getDocumentNumber());
                totalServiceActualPay = totalServiceActualPay + custDealList.get(i).getServiceActualPay() + custDealList.get(i).getDepositAmount();
                totalArrears = totalArrears + custDealList.get(i).getArrears();
            }
            //支付记录
            String payDealSql = "select t.outpatient_name as outpatientName,left(t.get_time,19) as getTime,t.is_do as isDo,t.tc_name as tcName,t.deal_status as dealStatus,t2.recharge_people " +
                    "as rechargePeople,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay, sum(t.arrears) as arrears," +
                    "(sum(t.folding_pay)+sum(t.arrears)) as tkfoldingPay,sum(t.deduction_amount) AS deductionAmount,sum(t.deposit_amount) as depositAmount," +   //退款=应收+欠费
                    "t2.remarks as remarks from (" + dealTables + ") t left join (select b.id2 as id2, b.recharge_people as recharge_people, " +
                    "b.operation_time as operation_time,b.remarks as remarks from (" + rechargeTables + ") b group by b.operation_time) t2 on t.get_time = t2.operation_time  where " +
                    "t.reservations_id='" + customerInfo1.getReservationsId() + "' and t.deal_status = '已结账' group by t.get_time order by t.get_time desc;";
            List<PayServicedInfo> payDealList = sqlMapper.selectList(payDealSql, PayServicedInfo.class);
            Double totalPayDealActualPay = 0.00;
            Double totalPayDealArrears = 0.00;
            for (int i = 0; i < payDealList.size(); i++) {
                totalPayDealActualPay =BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getServiceActualPay())).doubleValue();
                totalPayDealActualPay =BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getDepositAmount())).doubleValue();

                totalPayDealArrears =BigDecimal.valueOf(totalPayDealArrears).add(BigDecimal.valueOf(payDealList.get(i).getArrears())).doubleValue();
            }
            //欠费记录
            String arrearsDealSql = "select * from (select t.id2 as id2,t.reservations_id as reservationsId,t.customer_id as customerId," +
                    " t.deal_status as dealStatus,t.outpatient_name as outpatientName,t.tc_name as tcName,t.remarks as remarks,left(t.operation_time,19) as operationTime," +
                    " t.doctor as doctor,t.nurse as nurse,sum(t.deposit_amount) AS depositAmount,sum(t.deduction_amount) AS deductionAmount,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay)+sum(t.deposit_amount) as serviceActualPay,sum(t.arrears) as arrears,group_concat(distinct t1.pay_service_name) as payServiceName " +
                    " from (" + dealTables + ") t  left join " + ShiroKit.getUser().getPay_service_info() + " t1 on t.pay_service_id=t1.id where t.deal_status='已结账' group by t.operation_time ) t1 " +
                    " where t1.reservationsId = '" + customerInfo1.getReservationsId() + "' and t1.arrears >0 order by t1.operationTime desc;";
            List<PayServicedInfo> arrearsDealList = sqlMapper.selectList(arrearsDealSql, PayServicedInfo.class);
            Double totalArrearsAmount = 0.00;
            for (int i = 0; i < arrearsDealList.size(); i++) {
                totalArrearsAmount += arrearsDealList.get(i).getArrears();
            }
            //获取抵扣方式
            String checkoutSql = "select id,checkout_method from checkout_method_info where type in ('1','2')";
            List<CheckoutMethodInfo> checkoutMethodList = sqlMapper.selectList(checkoutSql, CheckoutMethodInfo.class);
            String checkoutMethodHtml = "";
            for (int i = 0; i < checkoutMethodList.size(); i++) {
                checkoutMethodHtml += "<option value='" + checkoutMethodList.get(i).getId() + "'>" + checkoutMethodList.get(i).getCheckoutMethod() + "</option>";
            }
            //查询储值、积分余额
            String noDealSql = "select * from " + tableHead + "deal_info where deal_status != '已结账' and reservations_id = '" + customerInfo1.getReservationsId() + "'";
            List<PayServicedInfo> noDealList = sqlMapper.selectList(noDealSql, PayServicedInfo.class);
            String jfbalancesql = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from return_cash_info where reservations_id='" + customerInfo1.getReservationsId() + "'";
            ScoreDealInfo scoreDealInfo = sqlMapper.selectOne(jfbalancesql, ScoreDealInfo.class);
            Double khmoneyBalance = customerInfo1.getMoneyBalance();
            BigDecimal b1 = new BigDecimal(Double.parseDouble(ShiroKit.getUser().getProportional()));
            BigDecimal b2 = new BigDecimal(Double.parseDouble(scoreDealInfo.getScoreBalance()));
            Double khjfbalance = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
            Double jfbalance = khjfbalance;
            for (int i = 0; i < noDealList.size(); i++) {
                BigDecimal m1 = new BigDecimal(Double.toString(khmoneyBalance));
                BigDecimal m2 = new BigDecimal(Double.toString(noDealList.get(i).getDepositAmount()));
                khmoneyBalance = m1.subtract(m2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                if (noDealList.get(i).getDeductionTypeName() != null) {
                    if ((noDealList.get(i).getDeductionTypeName()).indexOf("积分") >= 0) {
                        BigDecimal j1 = new BigDecimal(Double.toString(khjfbalance));
                        BigDecimal j2 = new BigDecimal(Double.toString(noDealList.get(i).getDeductionAmount()));
                        khjfbalance = j1.subtract(j2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                    }
                }
            }
            //项目
            model.addAttribute("payServiceInfoList", payServiceInfoList);
            ShiroKit.setSessionAttr("customerId", id);
            ShiroKit.setSessionAttr("reservationsId", customerInfo1.getReservationsId());
            List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList2(customerInfo1.getReservationsId());
            String tcName = "";
            String firstVisitDate = "";
            if (customerInfo1 != null) {
                tcName = customerInfo1.getFieldTc();
                firstVisitDate = customerInfo1.getFirstVisitDate();
            }
            if (consultantInfoList.size() > 0) {
                for (ConsultantInfo info1 : consultantInfoList) {
                    if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                        info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                        info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
                    }
                }
            }
            FirstCustomerInfo customerInfoFirst = customerService.selectOneByReservationsIdFirst(customerInfo1.getReservationsId(), customerTablesd);  //首次到店患者信息
            //就诊记录
            String sql11 = "select * from (" + customerTables + ") t order by t.startTime desc";
            List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
            for (CustomerInfo infos : customerInfoList) {
                infos.setOutpatientName(ShiroKit.getUser().getOutpatientName());
                if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                    infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                    infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
                }
            }
            //随访记录
            String sql4 = "select r.id,r.reservations_id,r.customer_id,r.create_name,r.revisit,r.revisit_time,r.create_time,r.update_time," +
                    "r.outpatient_name,r.revisit_content,r.tc_name,r.revisit_status,r.remarks,r.consumption_status,r.follow_status from return_tc_visit_info r " +
                    " where r.reservations_id ='" + customerInfo1.getReservationsId() + "' order by revisit_time desc";
            List<TcRevisitsInfo> tcRevisitInfoList = sqlMapper.selectList(sql4, TcRevisitsInfo.class);
            for (TcRevisitsInfo info1 : tcRevisitInfoList) {
                if (info1.getRevisitTime() != "" && info1.getRevisitTime() != null) {
                    info1.setRevisitTime(info1.getRevisitTime().substring(0, 11));
                }
            }
            //通过预约ID  获得开单记录
            String sql = "select t1.id as id,t1.customer_id as customerId,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus,t1.nurse  as nurse, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                    " t1.service_actual_pay as serviceActualPay ,left(t1.get_time,19) as operationTime,t1.remarks as remarks,t1.doctor as doctor,t1.primary_amount as primaryAmount," +
                    " t1.folding_pay as foldingPay,t1.pay_service as payService,t1.whole_discount as wholeDiscount,t1.discount_hide as discountHide," +
                    "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                    "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                    "t1.order_discount as orderDiscount,t1.deduction_type as deductionType," +
                    "t1.deduction_amount as deductionAmount,t1.deposit_amount as depositAmount,t1.deduction_typeName as deductionTypeName,t1.is_do as isDo ,t1.orthodontic_number as orthodonticNumber,t1.operation_time as operationTime " +
                    " from (" + dealTables + ") t1 left join " + ShiroKit.getUser().getPay_service_info() + " t2 on t1.pay_service_id=t2.id " +
                    " where t1.reservations_id='" + customerInfo1.getReservationsId() + "' and pay_service_id is not null group by operationTime order by operationTime desc";
            List<PayServicedInfo> payServiceInfoListS = sqlMapper.selectList(sql, PayServicedInfo.class);
            String getTime = "";
            Double totleReceivable = 0.00;
            Double totleFoldingPay = 0.00;
            Double totleServiceActualPay = 0.00;
            Double accountArrears = 0.00;
            for (PayServicedInfo info : payServiceInfoListS) {
                if (info != null) {
                    if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                        if (info.getPayServiceNum() != null) {
                            totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                        }
                        if (info.getFoldingPay() != null) {
                            totleFoldingPay += info.getFoldingPay();
                        }
                        if (info.getServiceActualPay() != null) {
                            totleServiceActualPay += info.getServiceActualPay();
                        }
                        if (info.getArrears() != null) {
                            accountArrears += info.getArrears();
                        }
                        info.setTotleReceivable(totleReceivable);
                        info.setTotleFoldingPay(totleFoldingPay);
                        info.setTotleServiceActualPay(totleServiceActualPay);
                        info.setAccountArrears(accountArrears);
                    }
                    if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                        info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                        info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                    }
                    getTime = info.getGetTime();
                }
                if (StringUtil.isNotEmpty(info.getOrthodonticNumber())) {
                    customerInfo1.setOrthodonticNumber(info.getOrthodonticNumber());
                }
            }
            //通过预约ID  执行记录
            String sqll = "select t1.id as id,t1.id2 as id2,t1.customer_id as customerId,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus,t1.pay_service_num as payServiceNum ," +
                    " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,left(t3.recharge_time,19) as rechargeTime,t1.remarks as remarks," +
                    " t1.arrears as arrears,t2.pay_service_name as payServiceName,t1.get_time as getTime from " + tableName1 + " t1 " +
                    " left join " + ShiroKit.getUser().getPay_service_info() + " t2 on t1.pay_service_id=t2.id left join " + tableName + " t3 on t3.id2=t1.id2 " +
                    " where t1.reservations_id='" + customerInfo1.getReservationsId() + "' and  t1.deal_status ='已结账'  group by  operationTime order by operationTime desc";
            List<PayServicedInfo> payServiceInfoList1 = sqlMapper.selectList(sqll, PayServicedInfo.class);
            for (PayServicedInfo info : payServiceInfoList1) {
                if (info != null) {
                    if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                        info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                        info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                    }
                    if (info.getRechargeTime() != "" && info.getRechargeTime() != null) {
                        info.setRechargeTimeOne(info.getRechargeTime().substring(0, 10));
                        info.setRechargeTimeTwo(info.getRechargeTime().substring(11, info.getRechargeTime().length()));
                    }
                }
            }
            //病例记录
            //1、先查询当前客户的所有病例记录
            String caseInfoSql = "select left(t1.finnally_time,19) as finnallyTime1,t1.* from case_info t1  " +
                    "where t1.reservations_id='" + customerInfo1.getReservationsId() + "'order by t1.finnally_time desc";
            List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);
            for (CaseInfo infos : caseInfoList) {
                if (infos != null) {
                    if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                        infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                    }
                    String sql3 = "select pay_service_name as payServiceName from " + ShiroKit.getUser().getPay_service_info() + " where id ='" + infos.getPayServiceId() + "'";
                    CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                    if (caseInfo1 == null) {
                        continue;
                    }
                    infos.setPayServiceName(caseInfo1.getPayServiceName());
                }
            }
            //2.循环遍历每个病例记录，给对应病例的牙位属性赋值
            for (CaseInfo caseInfo : caseInfoList) {
                //然后查询牙位集合
                String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
                List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
                for (ToothSite toothSite : toothSiteList) {
                    //根据对应的牙位类型，给病例实体的对应牙位属性赋值
                    if (toothSite.getYaType().equals("1")) {
                        caseInfo.getToothSite1List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("2")) {
                        caseInfo.getToothSite2List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("3")) {
                        caseInfo.getToothSite3List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("4")) {
                        caseInfo.getToothSite4List().add(toothSite);
                    }
                    if (toothSite.getYaType().equals("5")) {
                        caseInfo.getToothSite5List().add(toothSite);
                    }
                }
            }
            String selectsql = "select id from today_operation_info where reservations_id='" + customerInfo1.getReservationsId() + "' and " +
                    " left(operation_time,10)='" + DateTimeKit.now().substring(0, 10) + "' and operation_people='" + ShiroKit.getUser().getAccount() + "' " +
                    " and store='" + customerInfo1.getArrivalStore() + "'";
            List<DoctorOperationInfo> doctorOperationInfo = sqlMapper.selectList(selectsql, DoctorOperationInfo.class);
            if (doctorOperationInfo.size() == 0) { //是否操作过该患者
                String insertSql = " INSERT INTO today_operation_info  (customer_id,reservations_id,szm,operation_people,customer_name, " +
                        " customer_sex,customer_age, customer_phone,store,operation_time) values  " +
                        " ('" + id + "','" + customerInfo1.getReservationsId() + "','" + customerInfo1.getSzm() + "','" + ShiroKit.getUser().getAccount() + "','" + customerInfo1.getCustomerName() + "','" + customerInfo1.getCustomerSex() + "', " +
                        " '" + customerInfo1.getCustomerAge() + "','" + customerInfo1.getCustomerPhone() + "','" + customerInfo1.getArrivalStore() + "','" + DateTimeKit.now() + "') ";
                sqlMapper.insert(insertSql);//插入 今日操作数据 tc
            } else {
                String updatesql = "update today_operation_info set operation_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo1.getReservationsId() + "' and " +
                        " left(operation_time,10)='" + DateTimeKit.now().substring(0, 10) + "' and operation_people='" + ShiroKit.getUser().getAccount() + "'" +
                        " and store='" + customerInfo1.getArrivalStore() + "'";
                sqlMapper.update(updatesql);//修改 今日操作数据时间 tc
            }
            if (ShiroKit.getUser().getTcList() == null) {
                String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                        " where b.abbreviation='"+tableHead+"' and c.name like CONCAT('%','tc','%') ";
                List<TcInfo> tcList =sqlMapper.selectList(getListSql,TcInfo.class);
                ShiroKit.getUser().setTcList(tcList);
            }
            if (ShiroKit.getUser().getDepartmentList() == null) {
                String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
                List<DepartmentInfo> departmentList=sqlMapper.selectList(getListSql,DepartmentInfo.class);
                ShiroKit.getUser().setDepartmentList(departmentList);
            }
            if (ShiroKit.getUser().getDoctorInfoList() == null) {
                String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                        " where b.abbreviation='" + ShiroKit.getUser().getAbbreviation() + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
                List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
                ShiroKit.getUser().setDoctorInfoList(doctorInfoList);
            }
            model.addAttribute("doctorList", ShiroKit.getUser().getDoctorInfoList());
            if (ShiroKit.getSessionAttr("nursesList") == null) {
                String getListSql = " select a.id as id,a.name as nurse from user a left join dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                        " where a.status='1' and (b.abbreviation='" + tableHead + "' and c.name in ('护士','护士长','前台人员') and a.`status`='1')";
                ShiroKit.setSessionAttr("nursesList", sqlMapper.selectList(getListSql));
            }
            VipLevelInfo vipLevelInfo = memberProductService.getVipLevel(customerInfo1.getReservationsId());
            if(vipLevelInfo==null){
                customerInfo1.setVipLevelName("入门会员");
                customerInfo1.setDiscountDiscount(1);
                model.addAttribute("vipLevelName", "入门会员");
                model.addAttribute("discountDiscount", 1);
            }else{
                customerInfo1.setVipLevelName(vipLevelInfo.getVipLevelName());
                customerInfo1.setDiscountDiscount(vipLevelInfo.getDiscountDiscount());
                model.addAttribute("vipLevelName", vipLevelInfo.getVipLevelName());
                model.addAttribute("discountDiscount", vipLevelInfo.getDiscountDiscount());
            }
            String selsql = "select reservations_id,members_num from members_user where reservations_id='" + customerInfo1.getReservationsId() + "' limit 1";
            MembersUser membersUser = sqlMapper.selectOne(selsql, MembersUser.class);
            if (membersUser != null) {
                customerInfo1.setMembersNum(membersUser.getMembersNum());
            }
            model.addAttribute("membersNum", customerInfo1.getMembersNum());
            model.addAttribute("nursesList", ShiroKit.getSessionAttr("nursesList"));
            model.addAttribute("departmentList",ShiroKit.getUser().getDepartmentList());
            model.addAttribute("tcList", ShiroKit.getUser().getTcList());
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("roleNameLogin", ShiroKit.getUser().getRoleName());
            model.addAttribute("dealyesLogin", ShiroKit.getUser().getDealyes());
            model.addAttribute("customerId", id);
            model.addAttribute("recentlyCustomerStatus", customerInfoLast.getCustomerStatus());
            model.addAttribute("customerInfoFirst", customerInfoFirst);  //首次到店患者信息
            model.addAttribute("payServiceInfoList1", payServiceInfoList1);
            model.addAttribute("payServiceInfoListS", payServiceInfoListS);
            model.addAttribute("reservationsId", customerInfo1.getReservationsId());
            model.addAttribute("getTime", getTime);
            model.addAttribute("tcName", tcName);
            model.addAttribute("totalArrearsAmount", totalArrearsAmount);
            model.addAttribute("reservations", ShiroKit.getUser().getAccount());
            model.addAttribute("firstVisitDate", firstVisitDate);
            model.addAttribute("customerInfo1", customerInfo1);
            model.addAttribute("arrivalTimeStr", customerInfo1.getArrivalTime());
            model.addAttribute("jfbalance", jfbalance);
            String jfbalancesql1="select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from score_deal where reservations_id='"+customerInfo1.getReservationsId()+"'";
            ScoreDealInfo scoreDealInfo1=sqlMapper.selectOne(jfbalancesql1,ScoreDealInfo.class);
            model.addAttribute("jfbalancea", scoreDealInfo1.getScoreBalance());
            String getMoneyBalance = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_deal a " +
                    " where a.reservations_id='" + customerInfo1.getReservationsId() + "' limit 1";
            MembersUser membersUser1 = sqlMapper.selectOne(getMoneyBalance, MembersUser.class);
            model.addAttribute("moneyBalance", membersUser1.getMoneyBalance());
            String getOrthodonticsMoney = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_orthodontics_deal a " +
                    " where a.reservations_id='" + customerInfo1.getReservationsId() + "' limit 1";
            MembersUser membersUser2 = sqlMapper.selectOne(getOrthodonticsMoney, MembersUser.class);
            model.addAttribute("lowestPriceBill", ShiroKit.getUser().getLowestPriceBill());
            model.addAttribute("orthodonticsMoney", membersUser2.getMoneyBalance());
            model.addAttribute("khmoneyBalance", membersUser1.getMoneyBalance());
            model.addAttribute("khjfbalance", khjfbalance);
            model.addAttribute("consultantInfoList", consultantInfoList);
            model.addAttribute("customerInfoList", customerInfoList);
            model.addAttribute("caseInfoList", caseInfoList);
            model.addAttribute("custDealList", custDealList);
            model.addAttribute("payDealList", payDealList);
            model.addAttribute("arrearsDealList", arrearsDealList);
            model.addAttribute("tabId", tabId);
            model.addAttribute("isKz", isKz);
            model.addAttribute("tcRevisitInfoList", tcRevisitInfoList);
            model.addAttribute("outpatientName", ShiroKit.getUser().getOutpatientName());
            model.addAttribute("totalPayDealActualPay", totalPayDealActualPay);
            model.addAttribute("totalPayDealArrears", totalPayDealArrears);
            model.addAttribute("checkoutMethodHtml", checkoutMethodHtml);
            model.addAttribute("totalServiceActualPay", totalServiceActualPay);
            model.addAttribute("totalArrears", totalArrears);
            model.addAttribute("isRefundAdmin", ShiroKit.getUser().getIsRefundAdmin());
            if ("0".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1.html";
                } else {
                    return PREFIX + "consultantToDetail.html";
                }
            } else if ("1".equals(tabId)) {
                return PREFIX + "consultantToDetail_marketInfo.html";
            } else if ("2".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_openBill.html";
                } else {
                    return PREFIX + "consultantToDetail_openBill.html";
                }
            } else if ("3".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_billRecord.html";
                } else {
                    return PREFIX + "consultantToDetail_billRecord.html";
                }
            } else if ("4".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_consultRecord.html";
                } else {
                    return PREFIX + "consultantToDetail_consultRecord.html";
                }
            } else if ("5".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_jzRecord.html";
                } else {
                    return PREFIX + "consultantToDetail_jzRecord.html";
                }
            } else if ("6".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_image.html";
                } else {
                    return PREFIX + "consultantToDetail_image.html";
                }
            } else if ("7".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_case.html";
                } else {
                    return PREFIX + "consultantToDetail_case.html";
                }
            } else if ("8".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_bill.html";
                } else {
                    return PREFIX + "consultantToDetail_bill.html";
                }
            } else if ("9".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_kzsfRecord.html";
                } else {
                    return PREFIX + "consultantToDetail_kzsfRecord.html";
                }
            }  else if ("10".equals(tabId)) {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_settings.html";
                } else {
                    return PREFIX + "consultantToDetail_settings.html";
                }
            } else {
                if (reservationscInfo == null) {
                    return PREFIX + "consultantToDetail1_member.html";
                } else {
                    return PREFIX + "consultantToDetail_member.html";
                }
            }
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    //账单记录整单打印
    @RequestMapping(value = "/getOpenPrint1", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenPrint1(String operationTime, String getTime, String abbreviation, String reservationsId) {
        String tableName1 = abbreviation + "deal_info";
        String tableName3 = abbreviation + "customer_info";
        String sql22 = "select * from dept where abbreviation='" + abbreviation + "'";
        Dept dept = sqlMapper.selectOne(sql22, Dept.class);
        String tableNamep = dept.getAreaAbbreviation() + "pay_service_info";
        String sql = " select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName ," +
                " t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.whole_discount as wholeDiscount ,t1.pay_service_num as payServiceNum , sum(t1.service_actual_pay) as serviceActualPay ," +
                " left (t1.operation_time,19) as operationTime,t1.remarks as remarks,sum(t1.deposit_amount) as depositAmount, sum(t1.deduction_amount) as deductionAmount," +
                " sum(t1.folding_pay) as foldingPay,t1.doctor ,t1.left_Up leftUp ,t1.right_Up as rightUp,t1.left_Down as leftDown,(t1.pay_service_num*t1.pay_service) as primaryAmount," +
                " t1.right_Down as rightDown,t1.pay_service as payService,t2.id as payServiceId,t2.department as department,t2.unit as unit," +
                " t2.first_level_project as firstLevelProject,sum(t1.arrears) as arrears,t2.pay_service_name as payServiceName,t2.pay_service_price_one as payServicePriceOne," +
                " t2.pay_service_price_two as payServicePriceTwo,t1.type_number as typeNumber," +
                " t3.customer_name as customerName ,t3.medical_record_number as medicalRecordNumber,t3.customer_phone as customerPhone,t3.customer_age as customerAge," +
                "(" +
                " CASE t3.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex ,t3.arrival_store as arrivalStore,t3.field_tc as fieldTc,IFNULL((select sum(arrears) from " + tableName1 + " b " +
                " where b.reservations_id='" + reservationsId + "' and b.deal_status ='已结账' and get_time=(select get_time from " + tableName1 + " b  where b.reservations_id='" + reservationsId + "' " +
                " and b.deal_status ='已结账' and b.get_time<'" + getTime + "'" +
                " order by get_time desc limit 1)),0.00) " +
                " as scarrears from " + tableName1 + " t1 left join " +tableNamep+ " t2" +
                " on t1.pay_service_id=t2.id left join " + tableName3 + " t3 on t1.customer_id=t3.id where 1=1" +
                " and t1.operation_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "' group by t1.id2";
        String tableName2 = abbreviation + "recharge_info";
        String sql2 = "select document_number from " + tableName2 + " where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + getTime + "' and isEarnes!=9 and isEarnes!=7 and deal_id is not null limit 1;";
        PayServiceInfo openPrintList1 = sqlMapper.selectOne(sql2, PayServiceInfo.class);
        List<PayServicedInfo> openPrintList = sqlMapper.selectList(sql, PayServicedInfo.class);
        openPrintList.get(0).setDocumentNumber(openPrintList1.getDocumentNumber());
        return openPrintList;
    }

    @RequestMapping(value = "/getdcsByCustomerId", method = RequestMethod.POST)
    @ResponseBody
    public int getdcsByCustomerId(String id) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select t.id from distinction_customer_status t left join " + ShiroKit.getUser().getCustomer_info() + " t1" +
                " on t.reservations_id=t1.reservations_id and t.status=1  where t1.id = '" + id + "' limit 1";
        SfInfo info = sqlMapper.selectOne(abbreviationSql, SfInfo.class);
        if (info != null) {
            return 1;
        } else {
            return 0;
        }
    }

    @RequestMapping(value = "/getdcsByReservationId", method = RequestMethod.POST)
    @ResponseBody
    public int getdcsByReservationId(String id) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select t.id from distinction_customer_status t where t.reservations_id = '" + id + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        if (abbreviationList.size() > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @RequestMapping("/sumBorrowAmount")
    @ResponseBody
    public HashMap sumBorrowAmount(@RequestParam(required = false) String checkoutMethod,
                                   @RequestParam(required = false) String reach,
                                   @RequestParam(required = false) String fieldTc,
                                   @RequestParam(required = false) String doctor,
                                   @RequestParam(required = false) String startTime,
                                   @RequestParam(required = false) String customerStatus,
                                   @RequestParam(required = false) String endTime) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName1 = tableHead + "recharge_info";
        String sql = " select sum(t1.actualAccountBalance) as borrowAmount_sum from (select t1.id as id,t1.customer_id as customerId ,t1.record_people as recordPeople, " +
                " t1.reservations_id as reservationsId,t1.operation_time as operationTime,t1.checkout_method as checkoutMethod,t1.recharge_time as rechargeTime, " +
                " t1.remarks as remarks , t2.customer_name as customerName ,t2.customer_phone as customerPhone,t2.doctor as doctor, " +
                " t1.record_time as recordTime,t1.actual_account_balance as actualAccountBalance, t2.customer_status as customerStatus, " +
                " t2.customer_sex as customerSex ,t2.customer_age as customerAge,t2.field_tc as fieldTc " +
                " from " + tableName1 + " t1 left join " + ShiroKit.getUser().getCustomer_info() + " t2 on t1.customer_id=t2.id " +
                " left join checkout_method_info t3 on t1.checkout_method_id =t3.id " +
                " where 1=1 and t3.charge_bill_is_inCash=1";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (t2.customer_name like CONCAT('%','" + reach + "','%') or t2.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " t2.wechat_number like CONCAT('%','" + reach + "','%') or t2.medical_record_number like CONCAT('%','" + reach + "','%') or " +
                    " t2.szm like CONCAT('%','" + reach + "','%')) ";
        }
        if (StringUtil.isNotEmpty(fieldTc)) {
            String fieldTcs[] = fieldTc.split(",");
            fieldTc = "";
            if (fieldTcs.length == 1) {
                fieldTc = fieldTcs[0];
            } else {
                for (int i = 0; i < fieldTcs.length; i++) {
                    if (i == (fieldTcs.length - 1)) {
                        fieldTc += "'" + fieldTcs[i];
                    } else if (i == 0) {
                        fieldTc += fieldTcs[i] + "'" + ",";
                    } else {
                        fieldTc += "'" + fieldTcs[i] + "'" + ",";
                    }
                }
            }
            sql += " and t2.field_tc in ('" + fieldTc + "')";
        }
        if (StringUtil.isNotEmpty(customerStatus)) {
            String customerStatuss[] = customerStatus.split(",");
            customerStatus = "";
            if (customerStatuss.length == 1) {
                customerStatus = customerStatuss[0];
            } else {
                for (int i = 0; i < customerStatuss.length; i++) {
                    if (i == (customerStatuss.length - 1)) {
                        customerStatus += "'" + customerStatuss[i];
                    } else if (i == 0) {
                        customerStatus += customerStatuss[i] + "'" + ",";
                    } else {
                        customerStatus += "'" + customerStatuss[i] + "'" + ",";
                    }
                }
            }
            sql += " and t2.customer_status in ('" + customerStatus + "')";
        }
        if (StringUtil.isNotEmpty(doctor)) {
            sql += " and t2.doctor = '" + doctor + "'";
        }
        if (StringUtil.isNotEmpty(startTime)) {
            sql += " and t1.recharge_time >= '" + startTime + "'";
        }
        if (StringUtil.isNotEmpty(endTime)) {
            sql += " and t1.recharge_time <= '" + endTime + "'";
        }
        sql += "  ) t1   where 1=1 ";
        if (StringUtil.isNotEmpty(checkoutMethod)) {
            String checkoutMethods[] = checkoutMethod.split(",");
            checkoutMethod = "";
            if (checkoutMethods.length == 1) {
                checkoutMethod = checkoutMethods[0];
            } else {
                for (int i = 0; i < checkoutMethods.length; i++) {
                    if (i == (checkoutMethods.length - 1)) {
                        checkoutMethod += "'" + checkoutMethods[i];
                    } else if (i == 0) {
                        checkoutMethod += checkoutMethods[i] + "'" + ",";
                    } else {
                        checkoutMethod += "'" + checkoutMethods[i] + "'" + ",";
                    }
                }
            }
            sql += " and t1.checkoutMethod in ('" + checkoutMethod + "')";
        }
        sql += " order by t1.recordTime DESC;";
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("borrowAmount_sum", result.get("borrowAmount_sum"));
        } else {
            map.put("borrowAmount_sum", "0");
        }
        return map;
    }

    public List<PayServicedInfo> getDealInfoList(String operationTime, String reservationsId) throws Exception {
        if (ToolUtil.isEmpty(reservationsId) || ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,left(t1.get_time,19) as getTime,t1.is_do as isDo,t1.deal_status as dealStatus," +
                "t1.doctor1 as doctor,t1.nurse1 as nurse,t1.order_discount as orderDiscount,t1.deductionAmount as deductionAmount,t1.remarks as remarks,t1.serviceActualPay as serviceActualPay," +
                "t2.recharge_people as rechargePeople,t2.checkout_method as checkoutMethod," +
                "t2.document_number as documentNumber,t1.tc_name as tcName,left(t1.operation_time,19) as operationTime from (select t.id2 as id,t.outpatient_name as outpatient_name," +
                "t.get_time as get_time,t.tc_name as tc_name,t.is_do as is_do,t.deal_status as deal_status,GROUP_CONCAT(DISTINCT t.doctor) AS doctor1,GROUP_CONCAT(DISTINCT t.nurse) AS nurse1,(sum(t.service_actual_pay)+sum(t.deposit_amount)) as serviceActualPay," +
                "t.order_discount as order_discount,sum(t.deduction_amount) as deductionAmount,t.remarks as remarks,t.operation_time as operation_time from (" + dealTables + ") t where t.operation_time='" + operationTime + "' and t.reservations_id " +
                "= '" + reservationsId + "' and t.deal_status='已结账' group by t.get_time) t1 left join " + "(select b.recharge_people as recharge_people," +
                "b.document_number as  document_number,group_concat(distinct b.checkout_method) as checkout_method, b.operation_time as operation_time from (" + rechargeTables + ") b " +
                "group by b.operation_time) t2 on t1.get_time = t2.operation_time order by t1.get_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }

    /**
     * TC姓名跳转页面 (获取开单操作  和 开单记录的 数据)   传一个客户ID和 预约ID
     */
    @RequestMapping(value = "/change_treatmentUnit/{tc}")
    public String changeTreatmentUnit(@PathVariable String tc, Model model) {
        String treatmentUnit = tc.split(",")[0];
        String customerId = tc.split(",")[1];
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList=sqlMapper.selectList(getListSql,DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("departmentList",ShiroKit.getUser().getDepartmentList());
        model.addAttribute("customerId", customerId);
        model.addAttribute("treatmentUnit", treatmentUnit);
        return PREFIX + "change_treatmentUnit.html";
    }

    /**
     * 统计查询
     */
    @RequestMapping(value = "/total", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> change(String param1, String param2, String param3, String param4, String param5,
                                       String param6, String param7, String param8, String param9) throws Exception {
        String contitionInfo = " AND t.customer_status != '作废' AND t.arrival_status in (2,3,4,5)";
        if (StringUtils.isNotBlank(param1)) {
            contitionInfo += " and t.arrival_time >= '" + param1 + "' ";
        }
        if (StringUtils.isNotBlank(param2)) {
            contitionInfo += " and t.arrival_time <= '" + param2 + "' ";
        }
        if (StringUtil.isNotEmpty(param3)) {
            String followStatus = "";
            String followStatus2[] = param3.split(",");
            if (followStatus2.length == 1) {
                followStatus = followStatus2[0];
            } else {
                for (int i = 0; i < followStatus2.length; i++) {
                    if (i == (followStatus2.length - 1)) {
                        followStatus += "'" + followStatus2[i];
                    } else if (i == 0) {
                        followStatus += followStatus2[i] + "'" + ",";
                    } else {
                        followStatus += "'" + followStatus2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.follow_status in ('" + followStatus + "') ";
        }
        if (StringUtil.isNotEmpty(param4)) {
            String consumptionStatus = "";
            String consumptionStatus2[] = param4.split(",");
            if (consumptionStatus2.length == 1) {
                consumptionStatus = consumptionStatus2[0];
            } else {
                for (int i = 0; i < consumptionStatus2.length; i++) {
                    if (i == (consumptionStatus2.length - 1)) {
                        consumptionStatus += "'" + consumptionStatus2[i];
                    } else if (i == 0) {
                        consumptionStatus += consumptionStatus2[i] + "'" + ",";
                    } else {
                        consumptionStatus += "'" + consumptionStatus2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.consumption_status in ('" + consumptionStatus + "') ";
        }
        if (StringUtils.isNotBlank(param5)) {
            contitionInfo += " and t.field_tc = '" + param5 + "' ";
        }
        if (StringUtils.isNotBlank(param7)) {
            contitionInfo += " and t.deal_status = '" + param7 + "' ";
        }
        if (StringUtils.isNotBlank(param8)) {
            contitionInfo += " and t.diagnosis_status = '" + param8 + "' ";
        }
        if (StringUtils.isNotBlank(param9)) {
            contitionInfo += " and t.treatment_unit = '" + param9 + "' ";
        }
        if (StringUtils.isNotBlank(param6)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + param6 + "','%') or t.customer_phone like CONCAT('%','" + param6 + "','%') or t.customer_phone2 like CONCAT('%','" + param6 + "','%') or " +
                    " t.wechat_number like CONCAT('%','" + param6 + "','%') or t.medical_record_number like CONCAT('%','" + param6 + "','%') or t.szm like CONCAT('%" + param6 + "%')) ";
        }
        String sql = "select count(*) as countNum from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where 1=1 "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where  t.customer_status='新客首次' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where t.customer_status='新客多次' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where t.customer_status='复诊' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                " union all select count(*) as countNum  from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where  t.customer_status='老客' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb " +

                //复诊人次
                " union all select count(*) as countNum  from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where t.customer_status !='新客首次' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status, left(arrival_time,10) ) bb " +

                //未设置回访
                " union all select count(*) as countNum  from (select t.id  from " + ShiroKit.getUser().getCustomer_info() + " t  where t.customer_status ='新客首次' and t.first_return_settings='0' "
                + contitionInfo + " group by t.reservations_id,t.treatment_unit,t.customer_status ) bb ";

        List<ConsultantInfo> tongJiInfoList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return tongJiInfoList;
    }
}